import os
import mysql.connector
from dotenv import load_dotenv
from elevenlabs_tts import text_to_speech_file

# Load environment variables
load_dotenv()

# Database configuration
DB_CONFIG = {
    'user': os.getenv('DB_USER'),
    'password': os.getenv('DB_PASSWORD'),
    'host': os.getenv('DB_HOST'),
    'database': os.getenv('DB_NAME')
}

# Audio file directory
AUDIO_DIR = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'static', 'audio')


def generate_audio_for_words():
    """
    Generate audio files for words in the wordlist table and update the audio field.
    """
    # Ensure the audio directory exists
    os.makedirs(AUDIO_DIR, exist_ok=True)

    # Connect to the database
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor(dictionary=True)

    try:
        # Fetch words without audio
        # cursor.execute(
        #     "SELECT word_id, english, audio FROM wordlist WHERE audio IS NULL OR audio = ''")
        cursor.execute(
            "SELECT word_id, english, audio FROM wordlist")
        words = cursor.fetchall()

        for word in words:
            # Generate a unique filename
            filename = f"{word['english'].lower().replace(' ', '_')}.mp3"
            file_path = os.path.join(AUDIO_DIR, filename)

            # Check if the audio file already exists
            if not os.path.exists(file_path):
                # Generate audio file
                voice_id = os.getenv("ELEVENLABS_VOICE_ID")
                text_to_speech_file(word['english'], file_path, voice_id)

                # Update the audio field in the database
                relative_path = os.path.join('audio', filename)
                cursor.execute(
                    "UPDATE wordlist SET audio = %s WHERE word_id = %s",
                    (relative_path, word['word_id'])
                )
                conn.commit()

                print(f"Generated audio for word: {word['english']}")
            else:
                print(f"Audio file already exists for word: {word['english']}")

    except mysql.connector.Error as err:
        print(f"Database error: {err}")
    finally:
        cursor.close()
        conn.close()


if __name__ == "__main__":
    generate_audio_for_words()
